<?php
/**
 * Created by JetBrains PhpStorm.
 * User: Grom Hellscream
 * Date: 11/2/12
 * Time: 8:41 AM
 * To change this template use File | Settings | File Templates.
 */
// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * Chitietphong Model
 */
class VastModelChitietphong extends JModelItem
{
    /**
     * @var string phong
     */
    protected $phong;
    protected $donvi;

    /**
     * Get the message
     * @return string The message to be displayed to the user
     */
    public function getPhong()
    {
        $id = JRequest::getInt('id');

        $db = JFactory::getDBO();
        $query = $db->getQuery(true);

        $query->select('a.*');
        $query->from('#__phong AS a');
        $query->where('a.id = ' . $id);

        $db->setQuery($query);
        $this->phong = $db->loadObjectList();
        
        return $this->phong;
    }

    public function getKhoi() 
    {
        $id = JRequest::getInt('id');
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);
        
        $query->select('a.*');
        $query->from('#__phong as a');
        $query->where('a.id = ' . $id);
        
        $db->setQuery($query);
        $phong = $db->loadObject();
        
        if ($phong !== NULL)
        {
            $donviId = $phong->donvi_id;
            $query = $db->getQuery(true);
            
            $query->select('a.*');
            $query->from('#__donvi as a');
            $query->where('a.id = ' . $donviId);
            
            $db->setQuery($query);

            $donvi = $db->loadObject(); 
            if ($donvi !== NULL)
            {
                $khoiId = $donvi->khoi_id;
                $query = $db->getQuery(true);
                
                $query->select('a.*');
                $query->from('#__khoi as a');
                $query->where('a.id = ' . $khoiId);
                
                $db->setQuery($query);
    
                return $db->loadObject();                
            }               
        }
        
        return NULL;
    }
        
    public function getDonvi() {
        
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);
        
        $query->select('a.*');
        $query->from('#__donvi as a');
        $query->where('a.id = ' . $this->phong[0]->donvi_id);
        
        $db->setQuery($query);
        $this->donvi = $db->loadObject();
        
        return $this->donvi;
    }
    
    public function getLanhDaos()
    {
        $id = JRequest::getInt('id');
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);
        
        $query->select('a.*, b.chucdanh');
        $query->from('#__hosocanbo as a');
        $query->join('INNER', '#__congtac AS b ON a.id = b.hosocanbo_id');
        $query->where('b.phong_id = ' . $id);
        $query->where('b.taivi = 1');
        $query->where('b.lanhdao = 1');
        
        $db->setQuery($query);
        return $db->loadObjectList();        
    } 
    
    public function getThongTinNhanSu()
    {
        $id = JRequest::getInt('id');
        $result = array();
        
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);
        
        $query->select('DISTINCT a.*, b.chucdanh, COUNT(b.hosocanbo_id) AS socanbo');
        $query->from('#__hosocanbo as a');
        $query->join('INNER', '#__congtac AS b ON a.id = b.hosocanbo_id');
        $query->where('b.phong_id = ' . $id);
        $query->where('b.taivi = 1');

        $db->setQuery($query);
        $canbos = $db->loadObjectList();        
                
        if ($canbos !== NULL)
        {
            if (count($canbos) > 0)
            {
                $canbo = $canbos[0];
                $result['Số cán bộ trong phòng: '] = $canbo->socanbo;
            }
        }
        
        $query = $db->getQuery(true);
        
        $query->select('DISTINCT b.tinhtranglamviec_id, c.mota, COUNT(b.hosocanbo_id) AS socanbo');
        $query->from('#__hosocanbo as a');
        $query->join('INNER', '#__congtac AS b ON a.id = b.hosocanbo_id');
        $query->join('INNER', '#__tinhtranglamviec AS c ON b.tinhtranglamviec_id = c.id');
        $query->group('b.tinhtranglamviec_id, c.mota');
        $query->where('b.phong_id = ' . $id);
        $query->where('b.taivi = 1');

        $db->setQuery($query);
        $canbos = $db->loadObjectList();        
                
        if ($canbos !== NULL)
        {
            if (count($canbos) > 0)
            {
                foreach ($canbos as $canbo)
                {
                    $result['Số cán bộ ' . $canbo->mota . ' trong phòng: '] = $canbo->socanbo;                    
                }
            }
        }

        $query = $db->getQuery(true);
        
        $query->select('DISTINCT a.hocvi_id, c.ten, COUNT(b.hosocanbo_id) AS socanbo');
        $query->from('#__hosocanbo as a');
        $query->join('INNER', '#__congtac AS b ON a.id = b.hosocanbo_id');
        $query->join('INNER', '#__hocvi AS c ON a.hocvi_id = c.id');
        $query->group('a.hocvi_id, c.ten');
        $query->where('b.phong_id = ' . $id);
        $query->where('b.taivi = 1');

        $db->setQuery($query);
        $canbos = $db->loadObjectList();        
                        
        if ($canbos !== NULL)
        {
            if (count($canbos) > 0)
            {
                foreach ($canbos as $canbo)
                {
                    $result['Số ' . $canbo->ten . ' trong phòng: '] = $canbo->socanbo;                    
                }
            }
        }
        
        $query = $db->getQuery(true);
        
        $query->select('DISTINCT a.hocham_id, c.ten, COUNT(b.hosocanbo_id) AS socanbo');
        $query->from('#__hosocanbo as a');
        $query->join('INNER', '#__congtac AS b ON a.id = b.hosocanbo_id');
        $query->join('INNER', '#__hocham AS c ON a.hocham_id = c.id');
        $query->group('a.hocham_id, c.ten');
        $query->where('b.phong_id = ' . $id);
        $query->where('b.taivi = 1');

        $db->setQuery($query);
        $canbos = $db->loadObjectList();        
                
        if ($canbos !== NULL)
        {
            if (count($canbos) > 0)
            {
                foreach ($canbos as $canbo)
                {
                    $result['Số ' . $canbo->ten . ' trong phòng: '] = $canbo->socanbo;                    
                }
            }
        } 
        return $result;        
    }          
}